﻿using System;
using System.Collections.Generic;
using CSharpHelpers.Encodes;
using MySql.Data.MySqlClient;

namespace CSharpHelpers.DBAccess
{
    class MemberTableAccess : Access
    {
        /// <summary>
        ///鳳垀衄陓洘
        /// </summary>
        /// <returns></returns>
        public List<MemberTable> GetAllMemberTable(List<string> memberList)
        {
            string sql = "select * from member_table where 1=1 {0}";
            string sqlMember = string.Empty;
            foreach (string str in memberList)
            {
                sqlMember += " member_id='" + str + "' or";
            }

            if (memberList.Count != 0)
            {
                sqlMember = sqlMember.TrimEnd('o', 'r');
                sqlMember = " and (" + sqlMember + ")";
            }
            sql = string.Format(sql, sqlMember);

            List<MemberTable> list = new List<MemberTable>();

            using (MySqlDataReader reader = MySqlUtil.GetDataReader(sql, null, DBConst.DB_MemberShip))
            {
                while (reader.Read())
                {

                    list.Add(GetAllReader(reader));
                }
            }

            return list;
        }

        public List<MemberTable> GetRankOfMemberLoyalty(DateTime start, DateTime end, int rank, bool isOrderByCurrent)
        {
            string sql = "select * from member_table where {0} order by {1} desc limit {2}";

            string sqlTime = GetTimeSQL(start, end, "last_bet_time");
            string sqlOrderBy = isOrderByCurrent ? "loyalty_point" : "live_dealer_loyalty_point";


            sql = string.Format(sql, sqlTime, sqlOrderBy, rank);

            List<MemberTable> list = new List<MemberTable>();

            using (MySqlDataReader reader = MySqlUtil.GetDataReader(sql, null, DBConst.DB_MemberShip))
            {
                while (reader.Read())
                {

                    list.Add(GetAllReader(reader));
                }
            }

            return list;
        }

        /// <summary>
        /// 崝樓
        /// </summary>
        /// <returns>紱釬岆瘁傖髡</returns>
        public bool AddMemberTable(MemberTable entity)
        {
            string sql = @"insert into member_table(member_id,is_blacklisted,family_name,given_name,chinese_name,nationality,gender,date_of_birth,identity_card_type,identity_card_number,contact_phone_number,mobile_phone_number,home_address,email_address,loyalty_point,create_time,create_staff,active_card_id,last_bet_time,default_loyalty_point,redemption_loyalty_point,admin_adjustment_loyalty_point,slot_game_loyalty_point,rolling_loyalty_point,live_dealer_loyalty_point,password,game_credit,is_vip,photo,Location_Visa,Location_Birth,Professional_Nature,Identity_Type,Source_Payment,Address)
                           values(?member_id,?is_blacklisted,?family_name,?given_name,?chinese_name,?nationality,?gender,?date_of_birth,?identity_card_type,?identity_card_number,?contact_phone_number,?mobile_phone_number,?home_address,?email_address,?loyalty_point,?create_time,?create_staff,?active_card_id,?last_bet_time,?default_loyalty_point,?redemption_loyalty_point,?admin_adjustment_loyalty_point,?slot_game_loyalty_point,?rolling_loyalty_point,?live_dealer_loyalty_point,?password,?game_credit,?is_vip,?photo,?Location_Visa,?Location_Birth,?Professional_Nature,?Identity_Type,?Source_Payment,?Address);";

            MySqlParameter[] myParams = GetAllParameter(entity);

            if (MySqlUtil.ExecuteNonQuery(sql, myParams) <= 0)
            {
                return false;
            }

            return true;
        }

        /// <summary>
        /// 載陔笢恅靡趼
        /// </summary>
        /// <param name="memberId"></param>
        /// <param name="chineseName"></param>
        /// <returns></returns>
        public bool UpdateMChineseName(string memberId, string chineseName)
        {
            string name = LanguageHelper.GetLatinStr(chineseName);
            string sql = @"update member_table set chinese_name='" + name + "' where member_id ='" + memberId + "';";

            if (MySqlUtil.ExecuteNonQuery(sql, null) <= 0)
            {
                return false;
            }

            return true;
        }

        public MemberTable GetMemberTableByCardNumber(string card)
        {
            string sql = "select * from member_table where 1=1 {0}";

            string sqlCard = string.Empty;
            if (!string.IsNullOrEmpty(card))
            {
                sqlCard = "identity_card_number='" + card + "'";
            }

            sql = string.Format(sql, sqlCard);

            MemberTable entity = new MemberTable();

            using (MySqlDataReader reader = MySqlUtil.GetDataReader(sql, null, DBConst.DB_MemberShip))
            {
                if (reader.Read())
                {
                    entity = GetAllReader(reader);
                }
            }

            return entity;
        }

        /// <summary>
        /// 跦擂MemberId脤戙暮翹
        /// </summary>
        /// <param name="MemberId"></param>
        /// <returns></returns>
        public MemberTable GetMemberTableByMemberId(string MemberId)
        {
            string sql = "select * from member_table where 1=1 {0}";

            string sqlMemberId = string.Empty;
            if (!string.IsNullOrEmpty(MemberId))
            {
                sqlMemberId = " and member_id = '" + MemberId + "'";
            }

            sql = string.Format(sql, sqlMemberId);

            MemberTable entity = new MemberTable();

            using (MySqlDataReader reader = MySqlUtil.GetDataReader(sql, null, DBConst.DB_MemberShip))
            {
                if (reader.Read())
                {
                    entity = GetAllReader(reader);
                }
            }

            return entity;
        }

        /// <summary>
        /// 跦擂MemberID脤戙杅擂踱ㄛ瓚剿岆瘁湔婓笭葩MemberID
        /// </summary>
        /// <param name="MemberId"></param>
        /// <returns></returns>
        public bool isHaveMemberId(string MemberId)
        {
            string sql = "select member_id from member_table where 1=1 {0}";

            string sqlMemberId = string.Empty;
            if (!string.IsNullOrEmpty(MemberId))
            {
                sqlMemberId = " and member_id = '" + MemberId + "'";
            }

            sql = string.Format(sql, sqlMemberId);

            using (MySqlDataReader reader = MySqlUtil.GetDataReader(sql, null, DBConst.DB_MemberShip))
            {
                if (reader.Read())
                {
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// 跦擂phone number脤戙杅擂踱ㄛ瓚剿岆瘁湔婓笭葩phone number
        /// </summary>
        /// <returns>脤戙賦彆</returns>
        public List<MemberTable> GetAllMemberTableByPhoneNumber(string PhoneNumber)
        {
            string sql = "select * from member_table where 1=1 {0}";

            string sqlPhoneNumber = string.Empty;
            if (!string.IsNullOrEmpty(PhoneNumber))
            {
                sqlPhoneNumber = " and contact_phone_number = '" + PhoneNumber + "'";
            }

            sql = string.Format(sql, sqlPhoneNumber);

            List<MemberTable> list = new List<MemberTable>();

            using (MySqlDataReader reader = MySqlUtil.GetDataReader(sql, null))
            {
                while (reader.Read())
                {
                    list.Add(GetAllReader(reader));
                }
            }

            return list;
        }

        /// <summary>
        /// 跦擂identityCardNumber脤戙杅擂踱ㄛ瓚剿岆瘁湔婓笭葩identityCardNumber
        /// </summary>
        /// <returns>脤戙賦彆</returns>
        public List<MemberTable> GetAllMemberTableByIdentityCardNumber(string identityCardNumber)
        {
            string sql = "select * from member_table where 1=1 {0}";

            string sqlIdentityCardNumber = string.Empty;
            if (!string.IsNullOrEmpty(identityCardNumber))
            {
                sqlIdentityCardNumber = " and identity_card_number = '" + identityCardNumber + "'";
            }

            sql = string.Format(sql, sqlIdentityCardNumber);

            List<MemberTable> list = new List<MemberTable>();

            using (MySqlDataReader reader = MySqlUtil.GetDataReader(sql, null))
            {
                while (reader.Read())
                {
                    list.Add(GetAllReader(reader));
                }
            }

            return list;
        }

        /// <summary>
        /// 跦擂member_id刉壺暮翹
        /// </summary>
        /// <returns>紱釬岆瘁傖髡</returns>
        public bool DeleteMemberTableById(string memberId)
        {
            string sql = @"delete from member_table where member_id=" + memberId;

            if (MySqlUtil.ExecuteNonQuery(sql, null) <= 0)
            {
                return false;
            }

            return true;
        }

        /// <summary>
        /// 脤戙
        /// </summary>
        /// <returns>脤戙賦彆</returns>
        public List<MemberTable> GetMemberTablePhotoByMemberID(string memberId)
        {
            string sql = "select photo from member_table where 1=1 {0}";

            string sqlMemberId = string.Empty;
            if (!string.IsNullOrEmpty(memberId))
            {
                sqlMemberId = " and member_id = '" + memberId + "'";
            }

            sql = string.Format(sql, sqlMemberId);

            List<MemberTable> list = new List<MemberTable>();

            using (MySqlDataReader reader = MySqlUtil.GetDataReader(sql, null))
            {
                while (reader.Read())
                {
                    list.Add(GetAllReader(reader));
                }
            }

            return list;
        }

        /// <summary>
        /// 鳳member_table郔湮member_id
        /// 跦擂趼僇杅硉湮苤植湮善苤齬唗
        /// </summary>
        /// <returns></returns>
        public string GetMaxMemberId()
        {
            string maxMemberId = string.Empty;
            string sql = "select member_id from member_table ORDER BY --member_id desc LIMIT 0,1";
            using (MySqlDataReader reader = MySqlUtil.GetDataReader(sql, null))
            {
                while (reader.Read())
                {
                    maxMemberId = GetAllReader(reader).MemberId;
                }
            }

            return maxMemberId;
        }

        /// <summary>
        /// 鳳member_table郔湮member_id
        /// 跦擂趼僇杅硉湮苤植湮善苤齬唗
        /// </summary>
        /// <returns></returns>
        public string GetMaxMemberId(string currentYear)
        {
            string maxMemberId = string.Empty;
            string sql = "select member_id from member_table where member_id REGEXP '^" + currentYear + "' ORDER BY --member_id desc LIMIT 0,1";
            using (MySqlDataReader reader = MySqlUtil.GetDataReader(sql, null))
            {
                while (reader.Read())
                {
                    maxMemberId = GetAllReader(reader).MemberId;
                }
            }

            return maxMemberId;
        }

        /// <summary>
        /// 蔚桶笢垀衄腔硉董跤妗极
        /// </summary>
        /// <returns>殿隙妗极</returns>
        private MemberTable GetAllReader(MySqlDataReader reader)
        {
            MemberTable entity = new MemberTable();
            entity.MemberId = CheckNull(reader["member_id"]);
            //entity.IsBlacklisted = CheckIntNull(reader["is_blacklisted"]);
            //entity.FamilyName = LanguageHelper.ConvertISOToBig5(CheckNull(reader["family_name"]));
            //entity.GivenName = LanguageHelper.ConvertISOToBig5(CheckNull(reader["given_name"]));
            //entity.ChineseName = LanguageHelper.ConvertISOToBig5(CheckNull(reader["chinese_name"]));
            //entity.Nationality = LanguageHelper.ConvertISOToBig5(CheckNull(reader["nationality"]));
            //entity.Gender = CheckCharNull(reader["gender"]);
            //entity.DateOfBirth = CheckDatetimeNull(reader["date_of_birth"]);
            //entity.IdentityCardType = LanguageHelper.ConvertISOToBig5(CheckNull(reader["identity_card_type"]));
            //entity.IdentityCardNumber = CheckNull(reader["identity_card_number"]);
            //entity.ContactPhoneNumber = CheckNull(reader["contact_phone_number"]);
            //entity.MobilePhoneNumber = CheckNull(reader["mobile_phone_number"]);
            //entity.HomeAddress = CheckNull(reader["home_address"]);
            //entity.EmailAddress = CheckNull(reader["email_address"]);
            //entity.LoyaltyPoint = CheckIntNull(reader["loyalty_point"]);
            //entity.CreateTime = CheckDatetimeNull(reader["create_time"]);
            //entity.CreateStaff = CheckNull(reader["create_staff"]);
            //entity.ActiveCardId = CheckNull(reader["active_card_id"]);
            //entity.LastBetTime = CheckDatetimeNull(reader["last_bet_time"]);
            //entity.DefaultLoyaltyPoint = CheckIntNull(reader["default_loyalty_point"]);
            //entity.RedemptionLoyaltyPoint = CheckIntNull(reader["redemption_loyalty_point"]);
            //entity.AdminAdjustmentLoyaltyPoint = CheckIntNull(reader["admin_adjustment_loyalty_point"]);
            //entity.SlotGameLoyaltyPoint = CheckIntNull(reader["slot_game_loyalty_point"]);
            //entity.RollingLoyaltyPoint = CheckIntNull(reader["rolling_loyalty_point"]);
            //entity.LiveDealerLoyaltyPoint = CheckIntNull(reader["live_dealer_loyalty_point"]);
            //entity.Password = CheckNull(reader["password"]);
            //entity.GameCredit = CheckIntNull(reader["game_credit"]);
            //entity.Photo = CheckByteArrayNull(reader["photo"]);
            return entity;
        }

        /// <summary>
        /// 蔚妗极董跤統杅
        /// </summary>
        /// <returns>殿隙統杅摩</returns>
        private MySqlParameter[] GetAllParameter(MemberTable entity)
        {
            MySqlParameter[] myParams = new MySqlParameter[]{
				new MySqlParameter("?member_id", entity.MemberId),
				new MySqlParameter("?is_blacklisted", entity.IsBlacklisted),
				new MySqlParameter("?family_name", entity.FamilyName),
				new MySqlParameter("?given_name", entity.GivenName),
                //new MySqlParameter("?chinese_name",LanguageHelper.GetGbkStr(entity.ChineseName)),
                new MySqlParameter("?chinese_name",entity.ChineseName),
				new MySqlParameter("?nationality", entity.Nationality),
				new MySqlParameter("?gender", entity.Gender),
				new MySqlParameter("?date_of_birth", entity.DateOfBirth),
				new MySqlParameter("?identity_card_type", LanguageHelper.ConvertBig52Iso(entity.IdentityCardType)),
				new MySqlParameter("?identity_card_number", entity.IdentityCardNumber),
				new MySqlParameter("?contact_phone_number", entity.ContactPhoneNumber),
				new MySqlParameter("?mobile_phone_number", entity.MobilePhoneNumber),
				new MySqlParameter("?home_address", entity.HomeAddress),
				new MySqlParameter("?email_address", entity.EmailAddress),
				new MySqlParameter("?loyalty_point", entity.LoyaltyPoint),
				new MySqlParameter("?create_time", DateTime.Now),
				new MySqlParameter("?create_staff", entity.CreateStaff),
				new MySqlParameter("?active_card_id", entity.ActiveCardId),
				new MySqlParameter("?last_bet_time", entity.LastBetTime),
				new MySqlParameter("?default_loyalty_point", entity.DefaultLoyaltyPoint),
				new MySqlParameter("?redemption_loyalty_point", entity.RedemptionLoyaltyPoint),
				new MySqlParameter("?admin_adjustment_loyalty_point", entity.AdminAdjustmentLoyaltyPoint),
				new MySqlParameter("?slot_game_loyalty_point", entity.SlotGameLoyaltyPoint),
				new MySqlParameter("?rolling_loyalty_point", entity.RollingLoyaltyPoint),
				new MySqlParameter("?live_dealer_loyalty_point", entity.LiveDealerLoyaltyPoint),
				new MySqlParameter("?password", entity.Password),
				new MySqlParameter("?game_credit", entity.GameCredit),
				new MySqlParameter("?luck_draw_last_datetime", entity.LuckDrawLastDateTime),
				new MySqlParameter("?is_vip", entity.IsVip),
                new MySqlParameter("?photo",entity.Photo), 
                new MySqlParameter("?Location_Visa", entity.LocationVisa),
				new MySqlParameter("?Location_Birth", entity.LocationBirth),
				new MySqlParameter("?Professional_Nature", LanguageHelper.ConvertBig52Iso(entity.ProfessionalNature)),
				new MySqlParameter("?Identity_Type", LanguageHelper.ConvertBig52Iso(entity.IdentityType)),
				new MySqlParameter("?Source_Payment", LanguageHelper.ConvertBig52Iso(entity.SourcePayment)),
				new MySqlParameter("?Address", LanguageHelper.ConvertBig52Iso(entity.Address))
			};

            return myParams;
        }
    }
}
